﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Data.Odbc
Public Class Conexion
    Dim cn As New SqlConnection("Data Source=localhost;Initial Catalog=Bdd_Ventas;Integrated Security=True")
    Private factory As DbProviderFactory
    Private str1 As [String] = "System.Data.SqlClient"
    Private dbconn1 As DbConnection
    Private dbcom1 As DbCommand
    Public Shared msj_sql_server As String = ""


    Public Sub New()
        Conectar("Data Source=localhost;Initial Catalog=Bdd_Ventas;Integrated Security=True")

    End Sub

    Public Sub Conectar(ByVal conexion As String)
        factory = DbProviderFactories.GetFactory(str1)
        dbconn1 = factory.CreateConnection()
        dbconn1.ConnectionString = conexion

        msj_sql_server = "La conexion a la base de datos fue exitosa en la base SQL Server"

    End Sub


    Public Sub Insertar_Actualizar_Eliminar(ByVal consulta As [String], ByVal accion As [String])
        Conectar("Data Source=localhost;Initial Catalog=Bdd_Ventas;Integrated Security=True")
        dbconn1.Open()
        dbcom1 = factory.CreateCommand()
        dbcom1.CommandText = consulta

        dbcom1.Connection = dbconn1
        dbcom1.ExecuteNonQuery()
        msj_sql_server = "Los datos fueron correctamente " + accion + " en la base de datos"

    End Sub

   


    Public Sub Seleccionar(ByVal txt As TextBox, ByVal consulta As String)
        Conectar("Data Source=localhost;Initial Catalog=Bdd_Ventas;Integrated Security=True")
        dbconn1.Open()
        dbcom1 = factory.CreateCommand()
        dbcom1.CommandText = consulta
        dbcom1.Connection = dbconn1
        Dim myReader As DbDataReader = dbcom1.ExecuteReader()
        While myReader.Read()
            Dim usuario As Integer = myReader.GetInt32(0)

            ' + " " + myReader.GetString(2) + " " + myReader.GetString(3) + " " + myReader.GetString(4) + " " + myReader.GetString(5))


            txt.Text = usuario
        End While
        myReader.Close()

    End Sub
   
    Public Sub SeleccionarLetras(ByVal txt As TextBox, ByVal consulta As String)
        Conectar("Data Source=localhost;Initial Catalog=Bdd_Ventas;Integrated Security=True")
        dbconn1.Open()
        dbcom1 = factory.CreateCommand()
        dbcom1.CommandText = consulta
        dbcom1.Connection = dbconn1
        Dim myReader As DbDataReader = dbcom1.ExecuteReader()
        While myReader.Read()
            Dim usuario As String = myReader.GetString(0)

            ' + " " + myReader.GetString(2) + " " + myReader.GetString(3) + " " + myReader.GetString(4) + " " + myReader.GetString(5))


            txt.Text = "" & usuario
        End While
        myReader.Close()

    End Sub

    Public Sub SeleccionarDouble(ByVal txt As TextBox, ByVal consulta As String)
        Conectar("Data Source=localhost;Initial Catalog=Bdd_Ventas;Integrated Security=True")
        dbconn1.Open()
        dbcom1 = factory.CreateCommand()
        dbcom1.CommandText = consulta
        dbcom1.Connection = dbconn1
        Dim myReader As DbDataReader = dbcom1.ExecuteReader()
        While myReader.Read()
            Dim usuario As Double = myReader.GetFloat(0)

            ' + " " + myReader.GetString(2) + " " + myReader.GetString(3) + " " + myReader.GetString(4) + " " + myReader.GetString(5))


            txt.Text = "" & usuario
        End While
        myReader.Close()

    End Sub


    Public Function IdClientes() As DataSet
        'hola


        Dim dap As New SqlDataAdapter("Select nombres_cliente from clientes", cn)
        Dim ds As New DataSet
        dap.Fill(ds, "clientes")
        Return ds
    End Function
    Public Function TablaTodoClientes() As DataSet
        Dim str As String = " Select * from clientes"

        Dim dap As New SqlDataAdapter(str, cn)
        Dim ds As New DataSet
        dap.Fill(ds, "clientes")
        Return ds
    End Function

    Public Function TablaClientes(ByVal nombres As String) As DataSet
        Dim str As String = " Select * from clientes where nombres_cliente='" & nombres & "'"

        Dim dap As New SqlDataAdapter(str, cn)
        Dim ds As New DataSet
        dap.Fill(ds, "clientes")
        Return ds
    End Function

    Public Function TablaUsuario() As DataSet
        Dim str As String = " Select * from usuarios"

        Dim dap As New SqlDataAdapter(str, cn)
        Dim ds As New DataSet
        dap.Fill(ds, "usuarios")
        Return ds
    End Function


    Public Function IdProductos() As DataSet

        Dim dap As New SqlDataAdapter("Select descripcion from productos", cn)
        Dim ds As New DataSet
        dap.Fill(ds, "productos")
        Return ds
    End Function
    Public Function TablaProductos(ByVal nombres As String) As DataSet
        Dim str As String = " Select * from productos where descripcion='" & nombres & "'"

        Dim dap As New SqlDataAdapter(str, cn)
        Dim ds As New DataSet
        dap.Fill(ds, "productos")
        Return ds
    End Function

    Public Function TablaTodoProductos() As DataSet
        Dim str As String = " Select * from productos"

        Dim dap As New SqlDataAdapter(str, cn)
        Dim ds As New DataSet
        dap.Fill(ds, "productos")
        Return ds
    End Function

    Public Function IdInventario() As DataSet

        Dim dap As New SqlDataAdapter("Select IdInventario from Inventario", cn)
        Dim ds As New DataSet
        dap.Fill(ds, "Inventario")
        Return ds
    End Function

    Public Function Idfactura2() As DataSet

        Dim dap As New SqlDataAdapter("Select idfactura from factura", cn)
        Dim ds As New DataSet
        dap.Fill(ds, "factura")
        Return ds
    End Function
    Public Function TablaInventarios(ByVal nombres As String) As DataSet
        Dim str As String = " select IdInventario, DescripcionInventario, Nombres, Fecha from inventario, Empleados where empleados.IdEmpleado=Inventario.idempleado and Fecha like '" & nombres & " %'"

        Dim dap As New SqlDataAdapter(str, cn)
        Dim ds As New DataSet
        dap.Fill(ds, "inventario")
        Return ds
    End Function
    Public Function TablaTodoInventario() As DataSet
        Dim str As String = " Select * from inventario"

        Dim dap As New SqlDataAdapter(str, cn)
        Dim ds As New DataSet
        dap.Fill(ds, "inventario")
        Return ds
    End Function
    Public Function TablaTodoFactura() As DataSet
        Dim str As String = " Select * from factura"

        Dim dap As New SqlDataAdapter(str, cn)
        Dim ds As New DataSet
        dap.Fill(ds, "factura")
        Return ds
    End Function


    Public Function TablaFactura(ByVal nombres As String) As DataSet
        Dim str As String = " Select * from factura where fecha='" & nombres & "'"

        Dim dap As New SqlDataAdapter(str, cn)
        Dim ds As New DataSet
        dap.Fill(ds, "factura")
        Return ds
    End Function
End Class



